/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Persistencia;

/**
 *
 * @author v3550
 */

import java.sql.*;


public class CriarBanco {
    
    public static void main(String[] args) {
      
      // cria banco cadastro e discografia  
      try 
      {
        criaBanco();
        System.out.println("DataBase criado");
      }catch(DAOException e)
      {
        System.out.println(e);
      }
    }

    
    public static void criaBanco() throws DAOException {
    
     try {
             Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
           
        } catch (ClassNotFoundException ex) {
            throw new DAOException("JdbcOdbDriver not found!!");
        }
     
      try {
        Connection con = DriverManager.getConnection("jdbc:derby:derbyDB;create=true");
        Statement sta = con.createStatement();
        String sql = " CREATE TABLE USUARIO ("
                + " ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),"
                + " NOME VARCHAR(100) NOT NULL,"
                + " EMAIL VARCHAR(50),"
                + " CONSTRAINT primary_key PRIMARY KEY (ID)"
                + ")";
        
     /*   String sql2 = "CREATE TABLE ARTIGO_LICITACAO ("
                + " ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), "
                + " ID_USUARIO  INT NOT NULL, "
                + " DATA_INICIO DATE NOT NULL, HORA_INICIO TIMESTAMP NOT NULL, "
                + " DATA_FIM DATE NOT NULL, HORA_FIM TIMESTAMP NOT NULL,"
                + " VALOR_BASE DOUBLE NOT NULL,"
                + " CONSTRAINT primary_key_art_lic PRIMARY KEY (id) ,"
                + "      CONSTRAINT FK_LICITACAO_USUARIO FOREIGN KEY (ID_USUARIO)"
	        + " REFERENCES USUARIO (ID)"
                + ")";
        */
        String sql3 = "CREATE TABLE ARTIGO_CATEGORIA ("
                + " ID INTEGER  NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1)  , "
                + " DESCRICAO VARCHAR(50) NOT NULL, "
                + " CONSTRAINT primary_key_art_cat PRIMARY KEY (ID))";
        
        String sql4 = "CREATE TABLE ARTIGO_LEILAO( "
                + " ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1) , "
                + " ID_CATEGORIA INT NOT NULL, "
                + " ID_USUARIO INT NOT NULL,"
                + " DESCRICAO VARCHAR(50), "
                + " QUANTIDADE INT, "
                + " DATA_INICIO DATE NOT NULL, HORA_INICIO TIME NOT NULL, "
                + " DATA_FIM DATE NOT NULL, HORA_FIM TIME NOT NULL,"
                + " VALOR_BASE DOUBLE NOT NULL,"
                + " VALOR_MAXIMO VARCHAR(1) NOT NULL,"
                + " ATIVO VARCHAR(1) DEFAULT 'S',"
                + " CONSTRAINT primary_key_art_leilao PRIMARY KEY (ID), "
                + "    CONSTRAINT FK_ARTIGO_CATEGORIA  FOREIGN KEY (ID_CATEGORIA)"
                + " REFERENCES ARTIGO_CATEGORIA (ID),"
                + "    CONSTRAINT FK_USUARIO_ARTIGO FOREIGN KEY (ID_USUARIO) "
                + " REFERENCES USUARIO (ID)"
                + ")";
        
        /*String sql5 = "CREATE TABLE LANCE( "
                + "ID INT PRIMARY KEY NOT NULL , "
               // + "ID_USUARIO INT NOT NULL,"
                + "ID_ARTIGO INT  NOT NULL,"
                + "DATA DATE NOT NULL, "
                + "HORA TIMESTAMP NOT NULL, "
                + "VALOR DOUBLE NOT NULL,"
             //   + "    CONSTRAINT FK_LANCE_USER  FOREIGN KEY (ID_USUARIO)"
             //   + " REFERENCES CATEGORIA(ID),"
                + "    CONSTRAINT FK_LANCE_ARTIGO FOREIGN KEY (ID_ARTIGO)"
                + " REFERENCES ARTIGO (ID)"
                + ")"; */
        
        String sql5 = "CREATE TABLE ARTIGO_LANCE( "
                + " ID_USUARIO INT , "
                + " ID_ARTIGO INT  NOT NULL,"
                + " DATA DATE NOT NULL, "
                + " HORA TIME NOT NULL, "
                + " VALOR DOUBLE NOT NULL,"
                + " PRIMARY KEY (ID_USUARIO, ID_ARTIGO , VALOR), "
                + "   CONSTRAINT FK_LANCE_ARTIGO FOREIGN KEY (ID_ARTIGO)"
                + " REFERENCES ARTIGO_LEILAO (ID)"
                + ")";        
                
        sta.executeUpdate(sql);
       // sta.executeUpdate(sql2);
        sta.executeUpdate(sql3);
        sta.executeUpdate(sql4);
        sta.executeUpdate(sql5);
        sta.close();
        con.close();
    } catch (SQLException ex) {
        throw new DAOException(ex.getMessage());
    }
  }

    private static Connection getConnection() throws SQLException {
        //derbyDB sera o nome do diretorio criado localmente
        return DriverManager.getConnection("jdbc:derby:derbyDB");
    }
    
}
